package com.fun.demo.fun.kayang;

import java.sql.*;
import java.time.LocalDateTime;

public class JDBCDemo {

//    private static final String URL = "jdbc:mysql://192.168.0.70:3306/HMV9";
//    private static final String USER = "hrsys";
//    private static final String PASSWORD = "Kayang@123";
//    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    /**
     * IP：172.18.10.12
     * Port：2883
     * DB：HRMV9
     * Tenant：hrsys
     * 用户名：hrsys@hrsys#Kayang
     * 密码：HRsystem@2023#
     */

    private static final String URL = "jdbc:oracle:thin:@//192.168.0.93:1521/ORCL";
    private static final String USER = "hrsys";
    private static final String PASSWORD = "hrsys";
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";


    //国产库
//    private static final String URL = "jdbc:opengauss://172.18.1.52:26000/postgres";
//    private static final String USER = "hrsys";
//    private static final String PASSWORD = "hrsys@123";
//    private static final String DRIVER = "org.opengauss.Driver";


//    private static final String URL = "jdbc:kingbase8://172.18.3.211:54322/hrmdev";
//    private static final String USER = "hrsys";
//    private static final String PASSWORD = "V2024jy!";
//    private static final String DRIVER = "com.kingbase8.Driver";


//    private static final String URL = "jdbc:postgresql://172.18.1.53:5432/hrmdev";
//    private static final String USER = "hrsys";
//    private static final String PASSWORD = "hrsys@123";
//    private static final String DRIVER = "org.postgresql.Driver";

//    private static final String URL = "jdbc:vastbase://172.18.1.53:5432/hrmdev";
//    private static final String USER = "hrsys";
//    private static final String PASSWORD = "hrsys@123";
//    private static final String DRIVER = "cn.com.vastbase.Driver";
    public static void main(String[] args) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {

        Class.forName(DRIVER).newInstance();
//        getUserData( getConect()) ;
//
//        String procname = "iSP_importOrgGRADE";
//        getProcParam(procname.toUpperCase());
//        execProc(procname);
        exec();

    }

    private static void exec() throws SQLException {
        Connection connection = getConect();
        String sql = "insert into testphoto3 (ID,tphoto) select ?,? from dual where not exists(select 1 from testphoto3 where ID=?)";
        PreparedStatement statement = connection.prepareStatement(sql);

        String ids = "16";

        StringBuilder str = new StringBuilder();
        for (int i = 0; i < 4001; i++) {
            str.append("a");
        }
        byte[] data = str.toString().getBytes();
        System.out.println("length: " + data.length);
        statement.setString(1, ids);
        statement.setBytes(2, data);
        statement.setString(3, ids);
        int ret = statement.executeUpdate();
        System.out.println("ret: " + ret);
    }

    private static void query() throws SQLException {
        Connection connection = getConect();
        String sql = "select ?,?,?  from dual";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1, "abc111");
        statement.setObject(2, "abc222");
        statement.setObject(3, "abc333");
        int parcount = statement.getParameterMetaData().getParameterCount();

        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString(1);
            String id2 = resultSet.getString(2);
            String id3 = resultSet.getString(3);

            System.out.println(id + "-" + id2 + "-" + id3);
        }


        System.out.println(parcount);

    }

    public static void getUserData(Connection conn) {
        PreparedStatement pstmt = null;
        String xsql = "select id, account from skysecuser";
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(xsql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String account = rs.getString("account");
                System.out.println(id + " : " + account);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static Connection getConect() {
        Connection connection = null;
        try {
            // 建立数据库连接
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            if (connection != null) {
                System.out.println("Connected to the  database!");
            } else {
                System.out.println("Failed to make connection!");
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        return connection;
    }

    public static void getProcParam(String procedureName) {

        try {

            // 建立数据库连接
            Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);

            // 获取DatabaseMetaData
            DatabaseMetaData metaData = conn.getMetaData();

            ResultSet procParams = metaData.getProcedureColumns(null, null, procedureName, null);

//            String cols = "COLUMN_DEF, COLUMN_NAME, RADIX, LENGTH, .IS_NULLABLE, REMARKS, PRECISION, .SCALE, .SQL_DATA_TYPE, .SQL_DATETIME_SUB, .COLUMN_TYPE, PROCEDURE_CAT, COLUMN_TYPE, .RADIX, SQL_DATA_TYPE, .COLUMN_NAME, .NULLABLE, PROCEDURE_NAME, SCALE, DATA_TYPE, IS_NULLABLE, .PROCEDURE_CAT, .DATA_TYPE, .REMARKS, NULLABLE, .LENGTH, .CHAR_OCTET_LENGTH, .SPECIFIC_NAME, SQL_DATETIME_SUB, PROCEDURE_SCHEM, .ORDINAL_POSITION, .PRECISION, SPECIFIC_NAME," +
//                    " CHAR_OCTET_LENGTH, ORDINAL_POSITION, .COLUMN_DEF, .TYPE_NAME, TYPE_NAME, .PROCEDURE_NAME, .PROCEDURE_SCHEM";
//
//            String[] colsss = cols.split(",");
            // 处理结果集，打印参数信息
            System.out.println("Procedure Parameters for: " + procedureName);
            System.out.println("COLUMN_NAME - TYPE_NAME - DATA_TYPE ");
            while (procParams.next()) {
                System.out.println("===================================================");
                int colCount = procParams.getMetaData().getColumnCount();
                for (int i = 0; i < colCount; i++) {
                    String colname = procParams.getMetaData().getColumnLabel(i + 1);
                    Object val = procParams.getObject(colname);
                    System.out.println("col: " + colname + ", val: " + val);
                }
//                String paramName = procParams.getString("COLUMN_NAME");
//                String paramType = procParams.getString("TYPE_NAME");
//                Object dataType = procParams.getObject("DATA_TYPE");
//
//                System.out.println(paramName + " - " + paramType + " - " + dataType);
            }

            // 关闭资源
            procParams.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    private static String getDirection(int paramMode) {
        switch (paramMode) {
            case DatabaseMetaData.procedureColumnIn:
                return "IN";
            case DatabaseMetaData.procedureColumnInOut:
                return "INOUT";
            case DatabaseMetaData.procedureColumnOut:
                return "OUT";
            default:
                return "UNKNOWN";
        }
    }

    public static void execProc(String procedureName) {
        Connection conn = null;
        CallableStatement cs = null;
        try {
            // 建立数据库连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            String script = "{call " + procedureName + "(?,?,?,?,?,?,?)}";
            // 创建CallableStatement
            cs = conn.prepareCall(script);

            // 设置输入参数
//            cs.setString(1, "admin");
//            cs.setInt(2, 1);
//            cs.setString(3, "333");
//            cs.setString(4, "444");
//            cs.setString(5, "555");
//            cs.setInt(6, 123);

            //           cs.registerOutParameter(7, Types.INTEGER);

            // 执行存储过程
            boolean hasResult = cs.execute();

            // 检查是否有结果集
            if (hasResult) {
                ResultSet resultSet = cs.getResultSet();
                // 处理结果集，这里假设存储过程返回了结果集
                while (resultSet.next()) {
                    // 读取结果集中的数据，例如
                    int resultInt = resultSet.getInt(1);
                    String resultString = resultSet.getString(2);
                    System.out.println("Result: Int=" + resultInt + ", String=" + resultString);
                }
                resultSet.close();
            } else {
                // 如果存储过程没有返回结果集，可能有更新计数
                int rowsAffected = cs.getUpdateCount();
                System.out.println("Rows affected: " + rowsAffected);
            }

            // 关闭资源

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                try {
                    cs.close();
                } catch (SQLException e) {
                    System.out.println("cs e");
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    System.out.println("conn e");
                    e.printStackTrace();
                }
            }
        }
    }

}

